﻿using System;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace Commom
{
    /// <summary>
    /// 数据库操作帮助类
    /// </summary>
    public class DbHelper
    {
        /// <summary>
        /// 执行ExecuteNonQuery（可执行存储过程）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNoQuery(string sql,CommandType type=CommandType.Text,params IDbDataParameter[] parameters)
        {
            using (IDbConnection con= DbFactory.GetConnection())
            {
                using (IDbCommand cmd = DbFactory.GetCommand(sql,con))
                {
                    cmd.CommandType = type;
                    //parameters即使外部调用时，不传入参数，默认是个长度为零的数组
                    //这里的判断，为了防止可能从别人那里接收的参数为空的情况会报错
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }   
            }
        }

        /// <summary>
        /// 执行ExecuteScalar
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static Object ExecuteScalar(string sql, params IDbDataParameter[] parameters)
        {
            using (IDbConnection con = DbFactory.GetConnection())
            {
                using (IDbCommand cmd = DbFactory.GetCommand(sql, con))
                {
                    cmd.CommandType = CommandType.Text;
                    //parameters即使外部调用时，不传入参数，默认是个长度为零的数组
                    //这里的判断，为了防止可能从别人那里接收的参数为空的情况会报错
                    if (parameters != null)
                    {
                        foreach (var parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    con.Open();
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 获取查询结果的DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params IDbDataParameter[] parameters)
        {
            IDbDataAdapter adapter = DbFactory.GetDataAdapter(sql);
            adapter.SelectCommand.CommandType = CommandType.Text;
            //parameters即使外部调用时，不传入参数，默认是个长度为零的数组
            //这里的判断，为了防止可能从别人那里接收的参数为空的情况会报错
            if (parameters != null)
            {
                foreach (var parameter in parameters)
                {
                    adapter.SelectCommand.Parameters.Add(parameter);
                }
            }
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            return ds.Tables[0];
        }












        #region 扩展补丁
        /// <summary>
        /// 数据库驱动类型
        /// </summary>
        private enum DataProvider
        {
            Oracle,
            SqlServer,
            OleDb,
            Odbc,
            MySql
        }

        /// <summary>
        /// 数据库核心对象工厂
        /// </summary>
        private class DbFactory
        {
            /// <summary>
            /// 链接字符串
            /// </summary>
            private static string ConStr
            {
                get { return @"server=DESKTOP-CF3SS7N\Sql2005;database=SF_EDS;uid=sa;pwd=123123123"; }
            }

            /// <summary>
            /// 驱动类型
            /// </summary>
            private static DataProvider DataProvider
            {
                get
                {
                    return DataProvider.SqlServer;
                }
            }

            /// <summary>
            /// 获取Con对象
            /// </summary>
            /// <returns></returns>
            public static IDbConnection GetConnection()
            {
                IDbConnection iDbConnection;
                switch (DataProvider)
                {
                    case DataProvider.SqlServer:
                        iDbConnection = new SqlConnection(ConStr);
                        break;
                    case DataProvider.OleDb:
                        iDbConnection = new OleDbConnection(ConStr);
                        break;
                    case DataProvider.Odbc:
                        iDbConnection = new OdbcConnection(ConStr);
                        break;
                    case DataProvider.Oracle:
                        iDbConnection = new OracleConnection(ConStr);
                        break;
                    case DataProvider.MySql:
                        iDbConnection = new MySqlConnection(ConStr);
                        break;
                    default:
                        return null;
                }
                return iDbConnection;
            }

            /// <summary>
            /// 获取Command对象
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="con"></param>
            /// <returns></returns>
            public static IDbCommand GetCommand(string sql, IDbConnection con)
            {
                switch (DataProvider)
                {
                    case DataProvider.SqlServer:
                        return new SqlCommand(sql, (SqlConnection)con);
                    case DataProvider.OleDb:
                        return new OleDbCommand(sql, (OleDbConnection)con);
                    case DataProvider.Odbc:
                        return new OdbcCommand(sql, (OdbcConnection)con);
                    case DataProvider.Oracle:
                        return new OracleCommand(sql, (OracleConnection)con);
                    case DataProvider.MySql:
                        return new MySqlCommand(sql, (MySqlConnection)con);
                    default:
                        return null;
                }
            }

            /// <summary>
            /// 获取DataAdapter对象
            /// </summary>
            /// <returns></returns>
            public static IDbDataAdapter GetDataAdapter(string sql)
            {
                switch (DataProvider)
                {
                    case DataProvider.SqlServer:
                        return new SqlDataAdapter(sql, ConStr);
                    case DataProvider.OleDb:
                        return new OleDbDataAdapter(sql, ConStr);
                    case DataProvider.Odbc:
                        return new OdbcDataAdapter(sql, ConStr);
                    case DataProvider.Oracle:
                        return new OracleDataAdapter(sql, ConStr);
                    case DataProvider.MySql:
                        return new MySqlDataAdapter(sql, ConStr);
                    default:
                        return null;
                }
            }
        }
        #endregion

    }
}
